package com.guet.niit.Admin.dao;

import com.guet.niit.Admin.pojo.Agent;
import com.guet.niit.Login.pojo.User;
import com.guet.niit.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AgentDaolmpl {

    /**
     * 代理商添加
     * 1.SQL:insert into agent_user
     *
     */
    public boolean agentAdd(Agent agent){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtil.createConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            //Agent agent = new Agent();
            String sql = "INSERT INTO agent_user (id,username,password) "+
                    "VALUES("
                    + agent.getId()
                    + ",'"
                    + agent.getUsername()
                    + "','"
                    + agent.getPassword()
                    + "')";
            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn,stmt,rs);
        }
        return false;
    }


    /**
     * 代理商删除
     * 1.SQL:"DELETE FROM agent_user WHERE id=" + id;
     *
     * @return
     */
    public boolean agentDelete(Long id){

        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            connection = JDBCUtil.createConnection();
            // 获得Statement对象
            statement = connection.createStatement();
            // 发送SQL语句
            String sql = "DELETE FROM agent_user WHERE id=" + id;
            int num = statement.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(connection, statement, rs);
        }
        return false;
    }


    /**
     * 代理商修改
     * 1.SQL:"UPDATE charging set Username='" + agent.getUsername()
     *                      + "',password='" + agent.getPassword()
     *                     + "' WHERE id=" + agent.getId();
     *
     * @return
     */
    public boolean agentModify(Agent agent){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtil.createConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            //Agent agent = new Agent();
            String sql =  "UPDATE agent_user set username='" + agent.getUsername()
                     + "',password='" + agent.getPassword()
                    + "' WHERE id=" + agent.getId();

            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, stmt,rs );
        }
        return false;
    }



    /**
     * 代理商查询
     * 1.SQl:select * from agent_user;//查询表的所有记录
     *
     * @return
     */
    public List<Agent> agentInquire() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Agent agent = null;
        List<Agent> agents = new ArrayList<>();
        //        List<Agent> profits = null;
//        ArrayList<Agent> arrayList;
        try {
            connection = JDBCUtil.createConnection();
            //rs = null;
//            PreparedStatement preparedStatement = null;
            String sql = "select * from agent_user";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
//            preparedStatement = connection.prepareStatement(sql);
//            rs = preparedStatement.executeQuery();
//            arrayList = new ArrayList<>();
            while(rs.next()){
                long id = rs.getLong("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String photo = rs.getString("photo");
//              int chargingPile_id = rs.getInt("chargingPile_id");
//              agent.add(agent_id, charging_id, city, chargingPile_id);
                agent = new Agent(id, username, password,photo);
//                arrayList.add(agent);
                agents.add(agent);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(connection, statement, resultSet);
        }
        //return arrayList;
        return agents;
    }


    /**
     * id单独查询
     */

    /**
     * 查询所有代理商名通过在充电站表获取代理商id查询
     * @return
     */
    public List<Agent> selectAgentBeforeSelectStation() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Agent agent = null;
        List<Agent> agents = new ArrayList<>();

        try {
            Connection conn = JDBCUtil.createConnection();
            String sql = "select agent_user.username from agent_user inner join charging_stations on agent_user.id = charging_stations.agent_id";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                //获取数据
                String username = rs.getString("username");
//                long id = rs.getLong("id");
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//                String photo = rs.getString("photo");
                //封装login对象
//                agent = new Agent(id,username,password,photo);
                agent = new Agent();
                agent.setUsername(username);
                //装载集合
                agents.add(agent);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeConnection(connection,statement,resultSet);
        }
        return agents;
    }

    /**
     * 查询指定代理商名通过在充电站表获取代理商id查询
     * @return
     */
    public List<Agent> selectAgentBeforeSelectStation(Long agentId) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Agent agent = null;
        List<Agent> agents = new ArrayList<>();

        try {
            Connection conn = JDBCUtil.createConnection();
            String sql = "select agent_user.username from agent_user inner join charging_stations on agent_user.id = "+agentId;
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                //获取数据
                String username = rs.getString("username");
//                long id = rs.getLong("id");
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//                String photo = rs.getString("photo");
                //封装login对象
//                agent = new Agent(id,username,password,photo);
                agent = new Agent();
                agent.setUsername(username);
                //装载集合
                agents.add(agent);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeConnection(connection,statement,resultSet);
        }
        return agents;
    }

    /**
     * 通过代理商名查询代理商id
     * @param agentName
     * @return
     */
    public Agent selectAgentIdByAgentName(String agentName){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Agent agent = null;
//        List<Agent> agents = new ArrayList<>();

        try {
            Connection conn = JDBCUtil.createConnection();
            String sql = "select id from agent_user where username = \""+agentName+"\"";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                //获取数据
                Long id = rs.getLong("id");
//                long id = rs.getLong("id");
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//                String photo = rs.getString("photo");
                //封装login对象
//                agent = new Agent(id,username,password,photo);
                agent = new Agent();
                agent.setId(id);
                //装载集合
//                agents.add(agent);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeConnection(connection,statement,resultSet);
        }
        return agent;
    }
}
